package cn.ainit.sys.ds.store;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import javax.annotation.Resource;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.hibernate.SessionFactory;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import cn.ainit.sys.constant.TypesConstant;

@Repository
public class sysDataStore {
	public JdbcTemplate jdbcTemplate;
	
	public JdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}
	
	@Resource
	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}
	
	public SessionFactory sessionFactory;
	
	@Resource
	public void setSessionFactory(SessionFactory sessionFactory) {
		this.sessionFactory = sessionFactory;
	}
	
	public SessionFactory getSessionFactory() {
		return sessionFactory;
	}
	
	public int dbType = TypesConstant.DB_ORACLE_TYPE;	
 
	/***
	 *通用方法    执行sql语句 判断成功与失败
	 */
	public boolean executeSQL(String sql) throws Exception{
		try {
			this.getJdbcTemplate().execute(sql);
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException();
		}
	}
	
	/***
	 *通用方法    执行sql语句 返回执行结果对象
	 */
	public Object executeSQLRetObj(String sql) throws Exception{
		try {
			Object object = this.sessionFactory.getCurrentSession().createSQLQuery(sql);
			return object;
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException();
		}
	}
	
	/*****
	 *通用方法    执行存储过程
	 *procName PKG_ONIX_CREATOR.UP_CONVERT_ONIX21(i_root_value VARCHAR2,count Number, bC varchar2)
	 *Config   [   A+B+C ] 根据 ‘+’ 号插入数据
	 * @throws SQLException 
	 */
	public boolean executeOracleProc(String procName,String config) throws SQLException{
		Connection conn =null;
		CallableStatement cs =null;
		try {
			String callP = procName.split("\\(")[0];
			String[] callName = procName.split(",");
			String[] confS = config.split("\\+");
			if (callName.length != confS.length) {
				System.out.println("参数不匹配");
			}else{
				StringBuffer orclProcName = new StringBuffer("{call "+callP+"(");
				for (int i = 0; i < confS.length; i++) {
					orclProcName.append("?");
					if (i != confS.length - 1) {
						orclProcName.append(",");
					}
				}
				orclProcName.append(")}");
				conn = this.getJdbcTemplate().getDataSource().getConnection();
				cs = conn.prepareCall(orclProcName.toString());
				//分别给形参赋值
				for (int i = 0; i < confS.length; i++) {
					cs.setString(i, confS[i]);
				}
				cs.execute();
			}
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException();
		}finally{
			if(cs!=null){
				cs.close();
			}
			if(conn!=null){
				conn.close();
			}
		}
	}
	
	/***
	 *通用方法    更新表中数据  sql语句 赋值
	 */
	public boolean executeUpdateSql(String sqlText, String[] valuesArr,String[] whereValue)
			throws Exception {
		boolean info = false;
		Connection conn=null;
		PreparedStatement pstmt=null;
		try {
			conn=this.getJdbcTemplate().getDataSource().getConnection();
			pstmt=conn.prepareStatement(sqlText);
			int len = valuesArr.length;
			for (int i = 1; i <= len; i++) {
				pstmt.setString(i, valuesArr[i-1]);
			}
			for (int j = 1; j <= whereValue.length; j++) {
				pstmt.setString(j + len, whereValue[j-1]);
			}
			pstmt.execute();
			info = true;
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		}finally{
			if(pstmt!=null){
				pstmt.close();
			}
			if(conn!=null){
				conn.close();
			}
		}
		return info;
	}
	/***
	 *通用方法    执行sql语句 赋值
	 */
	public boolean executeSqlValues(String sqlTxt, String[] sqlParamStr)
			throws Exception {
		boolean info = false;
		Connection conn=null;
		PreparedStatement pstmt=null;
		try {
			conn=this.getJdbcTemplate().getDataSource().getConnection();
			pstmt=conn.prepareStatement(sqlTxt);
			for (int i = 1; i <= sqlParamStr.length; i++) {
				pstmt.setString(i, sqlParamStr[i-1]);
			}
			pstmt.execute();
			info = true;
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		}finally{
			if(pstmt!=null){
				pstmt.close();
			}
			if(conn!=null){
				conn.close();
			}
		}
		return info;
	}
	
	public List<String> callBackCol(String sqlText) throws Exception{
		Connection conn=null;
		PreparedStatement pstmt=null;
		try {
			List<String> list = new ArrayList<String>();
			conn=this.getJdbcTemplate().getDataSource().getConnection();
			pstmt=conn.prepareStatement(sqlText);
			ResultSet rs = pstmt.executeQuery();
			ResultSetMetaData rsmd = rs.getMetaData();
			for (int i = 1; i <= rsmd.getColumnCount(); i++) {
				list.add(rsmd.getColumnName(i));
			}
			return list;
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			if(pstmt!=null){
				pstmt.close();
			}
			if(conn!=null){
				conn.close();
			}
		}
		return null;
	}
	
	/***
	 * 加载对照信息
	 */
	public List queryDzSourceType(String sqlText){
		return this.getJdbcTemplate().queryForList(sqlText);
	}
	/***
	 * 获取主键 的值
	 * @param sequenceName
	 * @return
	 */
    protected String getPkStr(String sql) {  
        String value = "";
        List<Map<String, Object>> list = getJdbcTemplate().queryForList(sql);
        if (list!=null){
        	for (Iterator ite = list.get(0).entrySet().iterator(); ite.hasNext();) {
        		Entry<String,Object> entry = (Entry<String, Object>) ite.next();
        		value = (String) entry.getValue();
        	}
		}
        return value;  
    }  
    
    /**
     * 拼接查询列表界面的sql
     * @param dsid
     * @param sqlText
     * @param uid
     * @return
     * @throws Exception
     * @author Xuzq
     * @date 2014-5-20 下午04:39:59
     */
	public String formatDataSql(String dsid,String sqlText, String uid) throws Exception {
		StringBuffer tmpBuf = new StringBuffer(" select ");
		try {
			//数据取值的权限 
			String sql = "select NAME,INPUTTYPE_ADD,IS_MULTI_CLNS,MULTI_CLNS_TABLE,MULTI_PROC_FUNC,MULTI_CLNS_FK " +
					" FROM SYS_COLUMNS SC "+
			" WHERE   " +
			" (((SELECT COUNT(UG.GROUP_ID) FROM  SYS_PERMS_USER_GROUP UG WHERE UG.O_ID = '"+uid+"' AND UG.GROUP_ID ='1') = 1 ) " +
			" OR " +
			" (SC.NAME NOT IN ( " +
			" (SELECT GC.COLUMN_NAME FROM SYS_PERMS_GROUP_FUNC_COLN GC WHERE GC.DS_ID = '"+dsid+"'  AND GC.GROUP_ID IN  " +
			" (SELECT UG.GROUP_ID FROM SYS_PERMS_USER_GROUP UG WHERE UG.O_ID = '"+uid+"')) " +
			" UNION " +
			" ( SELECT UC.COLUMN_NAME FROM SYS_PERMS_USER_FUNC_COLN UC WHERE UC.DS_ID = '"+dsid+"'  AND UC.O_ID = '"+uid+"' ) " +
			" ) " +
			" )) " +
			" AND SC.DS_ID = '"+dsid+"'  "+
			" AND SC.ISSHOW = 'T' AND SC.JAVA_PROPERTY_RECORDED IS NOT NULL "+
			" ORDER BY SC.ORDER_NUM_ADD " ;
			
			List<Map<String, Object>> cliList = this.getJdbcTemplate().queryForList(sql);
			Map<String, Object> tmpCln = new HashMap<String, Object>();
			Object tmpObj = null;
			Object isMulti = null;
			Object mulProcFunc=null;
			Object intype = null;
			for (int i = 0; i < cliList.size(); i++) {
				tmpCln = cliList.get(i);
				tmpObj = tmpCln.get("NAME");
				isMulti = tmpCln.get("IS_MULTI_CLNS");
				mulProcFunc=tmpCln.get("MULTI_PROC_FUNC");
				intype = tmpCln.get("INPUTTYPE_ADD");
				//一对多的关系
				if (isMulti != null && "T".equals(isMulti.toString()) ) {//一对多字段获取的形式
					if(mulProcFunc!=null&&!"".equals(mulProcFunc.toString().trim())){
						tmpBuf.append(mulProcFunc+" "+tmpObj.toString()+" ");
					}else {
						tmpBuf.append("pk_sys_ui.uf_parse_ids('"+dsid+"','"+tmpObj.toString()+"',"+tmpObj.toString()+") "+tmpObj.toString()+" ");
					}
				}else{
					tmpBuf.append(tmpObj);
				}
				if (i < cliList.size() - 1) {
					tmpBuf.append(",");
				}
			}
			//根据dsid 和  uid 获取用户的数据的权限
			String fbDataSql = "select nvl((SELECT CONFIG_SQL FROM sys_perms_user_func_data " +
			" WHERE O_ID = '"+uid+"' AND DS_ID = '"+dsid+"'), "+
			" (select CONFIG_SQL from sys_perms_group_func_data where group_id "+
			" in (select g.group_id from sys_perms_user_group g where g.o_id = '"+uid+"') "+
			" and ds_id = '"+dsid+"')) CONFIG from dual";
			List<Map<String, Object>> fbData = this.getJdbcTemplate().queryForList(fbDataSql);
			String confSql = "";
			if (fbData != null && fbData.size() == 1) {
				if (fbData.get(0).get("CONFIG") != null && (!"".equals(fbData.get(0).get("CONFIG")))) {
					confSql = fbData.get(0).get("CONFIG") .toString();
					if (confSql.indexOf("$uid") > -1) {
						confSql = confSql.replaceAll("\\$uid", "'"+uid+"'"); //$ 符号为特殊符号，需要转译
					}
				}
			}
			tmpBuf.append(" from ("+sqlText +") ttmp " +confSql);
			System.out.println("SysBaseDao #L.230# "+tmpBuf.toString());
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException("读取数据，字段可能不存在["+e.getMessage()+"]，请检查Sys_columns.NAME中的字段和表中的sql是否一致");
		}
		return tmpBuf.toString();
	}
	
	/**
     * 拼接详情界面的sql
     * @param dsid
     * @param sqlText
     * @param uid
     * @return
     * @throws Exception
     * @author Xuzq
     * @date 2014-5-20 下午04:39:59
     */
	public String formatRowDataSql(String dsid,String sqlText, String uid) throws Exception {
		StringBuffer tmpBuf = new StringBuffer(" select ");
		try {
			String sql = "select DS_ID,NAME,MULTI_SAME_DS_ID,IS_MULTI_CLNS,MULTI_CLNS_TABLE,MULTI_CLNS_FK,MULTI_PROC_FUNC FROM SYS_COLUMNS SC "+
			" WHERE " +
			" (((SELECT COUNT(UG.GROUP_ID) FROM  SYS_PERMS_USER_GROUP UG WHERE UG.O_ID = '"+uid+"' AND UG.GROUP_ID ='1') = 1 ) " +
			" OR " +
			" (SC.NAME NOT IN ( " +
			" (SELECT GC.COLUMN_NAME FROM SYS_PERMS_GROUP_FUNC_COLN GC WHERE GC.DS_ID = '"+dsid+"'  AND GC.GROUP_ID IN  " +
			" (SELECT UG.GROUP_ID FROM SYS_PERMS_USER_GROUP UG WHERE UG.O_ID = '"+uid+"')) " +
			" UNION " +
			" ( SELECT UC.COLUMN_NAME FROM SYS_PERMS_USER_FUNC_COLN UC WHERE UC.DS_ID = '"+dsid+"'  AND UC.O_ID = '"+uid+"' ) " +
			" ) " +
			" )) " +
			" AND SC.DS_ID = '"+dsid+"'  "+
//			" DS_ID = '"+dsid+"' " +
//			" AND NAME NOT IN (select uc.column_name from sys_right_user_columns uc where uc.ds_id ='"+dsid+"' and uc.o_id = '"+uid+"' "+  
//			" union select gc.column_name from sys_right_group_columns gc where gc.group_id in "+
//			" (select g.group_id from sys_right_map g where g.o_id = '"+uid+"') and gc.ds_id = '"+dsid+"') " +
			" AND ISSHOW = 'T' AND JAVA_PROPERTY_RECORDED IS NOT NULL "+
			" ORDER BY ORDER_NUM_ADD" ;
			List<Map<String, Object>> cliList = this.getJdbcTemplate().queryForList(sql);
			Map<String, Object> tmpCln = new HashMap<String, Object>();
			Object tmpObj = null;
			Object isMulti = null;
			Object mulProcFunc=null;
			Object intype=null;
			Object maltiSameDsId=null;
			Object dsId=null;
			for (int i = 0; i < cliList.size(); i++) {
				tmpCln = cliList.get(i);
				tmpObj = tmpCln.get("NAME");
				isMulti = tmpCln.get("IS_MULTI_CLNS");
				mulProcFunc=tmpCln.get("MULTI_PROC_FUNC");  //一对多的时候调用自己个性的回调函数
				intype = tmpCln.get("INPUTTYPE_ADD");
				maltiSameDsId=tmpCln.get("MULTI_SAME_DS_ID");  
				dsId=tmpCln.get("DS_ID");
				if (isMulti != null && "T".equals(isMulti.toString())  ) {
					if(mulProcFunc!=null&&!"".equals(mulProcFunc.toString().trim())){
						tmpBuf.append(mulProcFunc+"||'-'||"+tmpObj.toString()+" "+tmpObj.toString()+" ");
					}else {
						tmpBuf.append("pk_sys_ui.uf_parse_ids('"+dsid+"','"+tmpObj.toString()+"',"+tmpObj.toString()+")||'-'||"+tmpObj.toString()+" "+tmpObj.toString()+" ");
					}
				}
//				else if (intype != null && "file".equals(intype.toString()) ) { //文件字段获取的形式
//					if(mulProcFunc!=null&&!"".equals(mulProcFunc.toString().trim())){
//						tmpBuf.append(tmpObj);
//					}else {
//						//需要的字段    dsid  pkvalue  clnName
//						//									    dsid             clnName			 claNameValue       clnName			
//						//tmpBuf.append("pk_sys_ui.uf_parse_ids('"+dsid+"','"+tmpObj.toString()+"',"+tmpObj.toString()+") "+tmpObj.toString()+" ");
//						String defStr = "<TABLE  cellpadding='0' cellspacing='0'  class='upfile'  cellpadding='0' cellspacing='0' >"+
//						"<TR><TD class='upfiletd'>ap0001.jpg</TD>"+
//						"<TD class='upfiletd'>2014-02-05 12:12:12</TD>"+
//						"<TD class='upfiletd'>" +
//						"重新上传&nbsp;&nbsp;删除&nbsp;&nbsp;预览&nbsp;&nbsp;下载" +
//						"</TD></TR></TABLE>";
//						tmpBuf.append("'默认文件.jpg '"+" " +tmpObj.toString()+" ");
//					}
//				}
				else{
					tmpBuf.append(tmpObj);
				}
				if (i < cliList.size() - 1) {
					tmpBuf.append(",");
				}
			}
			tmpBuf.append(" from ("+sqlText +") ttmp ");
			System.out.println("SysBaseDao #L.275# "+tmpBuf.toString());
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException("读取数据，字段可能不存在["+e.getMessage()+"]，请检查Sys_columns.NAME中的字段和表中的sql是否一致");
		}
		return tmpBuf.toString();
	}

	/**
	 * 
	 * @param url
	 *            保存 文件的位置
	 * @param String
	 *            [] topName 第一列的标签
	 * @param String
	 *            [] dbCellName 要查询导出的列名（和sql中必须一致）
	 * @param List
	 *            <Map<String, String>> 返回数据集合中必须是map集合
	 * @throws FileNotFoundException
	 */
	public static void exportEmployeeByPoi(String title,String url, String[] topName,
			String[] dbCellName, List<Map<String, String>> list)
			throws FileNotFoundException {
		OutputStream os = new FileOutputStream(url);
		try {
			// 创建Excel工作薄
			HSSFWorkbook book = new HSSFWorkbook();
			// 在Excel工作薄中建一张工作表
			HSSFSheet sheet = book.createSheet(title);
			// 设置单元格格式(文本)
			// HSSFCellStyle cellStyle = book.createCellStyle();
			HSSFCell cell = null;
			// 第一行为标题行
			HSSFRow row = sheet.createRow(0);// 创建第一行
			for (int i = 0; i < topName.length; i++) {

				cell = row.createCell(i);
				// 定义单元格为字符串类型
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(topName[i]);
			}

			// 循环导出数据到excel中
			for (int i = 0; i < list.size(); i++) {

				// 创建第i行
				row = sheet.createRow(i + 1);
				// 在第i行的相应列中加入相应的数据
				Map s = (Map) list.get(i);
				for (int j = 0; j < dbCellName.length; j++) {
					cell = row.createCell(j);
					// 定义单元格为字符串类型
					cell.setCellType(HSSFCell.CELL_TYPE_STRING);
					cell.setCellValue((s.get("" + dbCellName[j]+ "") == null ? "":(s.get("" + dbCellName[j]+ ""))).toString());
				}

			}
			// 写入数据 把相应的Excel 工作簿存盘
			book.write(os);
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	
	/**
     * format SQL
     * @param dsid
     * @param sqlText
     * @param uid
     * @return
     * @throws Exception
     * @author Xuzq
     * @date 2014-5-20 下午04:39:59
     */
	public String formatSqlTra(String dsid,String sqlText, String uid) throws Exception {
		StringBuffer tmpBuf = new StringBuffer(" select ");
		try {
			String sql = "select NAME,EDITYPE,IS_MULTI_CLNS,MULTI_CLNS_TABLE,MULTI_CLNS_FK FROM SYS_COLUMNS "+
			" WHERE DS_ID = '"+dsid+"' AND NAME NOT IN (select uc.column_name from sys_right_user_columns uc where uc.ds_id ='"+dsid+"' and uc.o_id = '"+uid+"' "+  
			" union select gc.column_name from sys_right_group_columns gc where gc.group_id in "+
			" (select g.group_id from sys_right_map g where g.o_id = '"+uid+"') and gc.ds_id = '"+dsid+"') AND ISSHOW = 'T' AND JAVA_PROPERTY_RECORDED IS NOT NULL "+
			" ORDER BY ORDER_NUM_ADD" ;
			List<Map<String, Object>> cliList = this.getJdbcTemplate().queryForList(sql);
			Map<String, Object> tmpCln = new HashMap<String, Object>();
			Object tmpObj = null;
			Object isMulti = null;
			Object editype = null;
			for (int i = 0; i < cliList.size(); i++) {
				tmpCln = cliList.get(i);
				tmpObj = tmpCln.get("NAME");
				isMulti = tmpCln.get("IS_MULTI_CLNS");
				editype = tmpCln.get("EDITYPE");
				if (isMulti != null && "T".equals(isMulti.toString()) ) {
					// pk_sys_ui.uf_parse_ids('ds_id','cln_name',fk_value) 
//					tmpBuf.append("pk_sys_ui.uf_parse_ids('"+dsid+"','"+tmpObj.toString()+"',"+tmpObj.toString()+")||'-'||"+tmpObj.toString()+" "+tmpObj.toString()+" ");
					tmpBuf.append("pk_sys_ui.uf_translate_ids('"+dsid+"','"+tmpObj.toString()+"','"+editype+"',"+tmpObj.toString()+",'0')"+" "+tmpObj.toString()+" ");
				}else if(editype != null && !"".equals(editype)){
					String[] valueStr=tmpObj.toString().split(";");
					String finalVal="";
					for (int j = 0; j < valueStr.length; j++) {
						finalVal +="'"+valueStr[j]+"'";
						if(j<valueStr.length-1){
							finalVal+=",";
						}
					}
					tmpBuf.append("pk_sys_ui.uf_translate_ids('"+dsid+"','"+tmpObj.toString()+"','"+editype+"',"+tmpObj.toString()+",'1')"+" "+tmpObj.toString()+" ");
				}else{
					tmpBuf.append(tmpObj);
				}
				if (i < cliList.size() - 1) {
					tmpBuf.append(",");
				}
			}
			tmpBuf.append(" from ("+sqlText +") ttmp ");
			System.out.println("SysBaseDao #L.275# "+tmpBuf.toString());
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException("读取数据，字段可能不存在["+e.getMessage()+"]，请检查Sys_columns.NAME中的字段和表中的sql是否一致");
		}
		return tmpBuf.toString();
	}
}
